On This Page

• Tool Parameters
• Alerts by Chronological Order
• 9/21/2012 3:04:39 PM - 9/21/2012 11:49:40 PM Alerts: (7)
• 9/21/2012 11:49:40 PM - 9/22/2012 8:34:41 AM Alerts: (18)
• 9/22/2012 8:34:41 AM - 9/22/2012 5:19:43 PM Alerts: (4)
• 9/22/2012 5:19:43 PM - 9/23/2012 2:04:43 AM Alerts: (11)
• 9/23/2012 2:04:43 AM - 9/23/2012 10:44:44 AM Alerts: (10)
• 9/23/2012 10:44:44 AM - 9/23/2012 7:29:46 PM Alerts: (7)
• 9/23/2012 7:29:46 PM - 9/24/2012 4:14:46 AM Alerts: (8)
• 9/24/2012 4:14:46 AM - 9/24/2012 12:59:47 PM Alerts: (8)
• 9/24/2012 12:59:47 PM - 9/24/2012 9:44:49 PM Alerts: (13)
• 9/24/2012 9:44:49 PM - 9/25/2012 6:24:50 AM Alerts: (9)
• 9/25/2012 6:24:50 AM - 9/25/2012 3:09:51 PM Alerts: (7)
• 9/25/2012 3:09:51 PM - 9/25/2012 11:54:52 PM Alerts: (7)
• 9/25/2012 11:54:52 PM - 9/26/2012 8:39:53 AM Alerts: (7)
• 9/26/2012 8:39:53 AM - 9/26/2012 5:24:54 PM Alerts: (11)
• 9/26/2012 5:24:54 PM - 9/27/2012 2:04:55 AM Alerts: (8)
• 9/27/2012 2:04:55 AM - 9/27/2012 10:49:56 AM Alerts: (7)
• 9/27/2012 10:49:56 AM - 9/27/2012 7:34:57 PM Alerts: (7)
• 9/27/2012 7:34:57 PM - 9/28/2012 4:19:58 AM Alerts: (7)
• 9/28/2012 4:19:58 AM - 9/28/2012 1:04:59 PM Alerts: (7)
• 9/28/2012 1:04:59 PM - 9/28/2012 9:45:00 PM Alerts: (7)
• 9/28/2012 9:45:00 PM - 9/29/2012 6:30:00 AM Alerts: (18)
• 9/29/2012 6:30:00 AM - 9/29/2012 3:15:02 PM Alerts: (2)
• 9/29/2012 3:15:02 PM - 9/30/2012 12:00:03 AM Alerts: (8)
• 9/30/2012 12:00:03 AM - 9/30/2012 8:45:04 AM Alerts: (13)
• 9/30/2012 8:45:04 AM - 9/30/2012 5:25:05 PM Alerts: (8)
• 9/30/2012 5:25:05 PM - 10/1/2012 2:10:06 AM Alerts: (6)
• 10/1/2012 2:10:06 AM - 10/1/2012 10:55:07 AM Alerts: (7)
• 10/1/2012 10:55:07 AM - 10/1/2012 7:40:08 PM Alerts: (11)
• 10/1/2012 7:40:08 PM - 10/2/2012 4:25:09 AM Alerts: (8)
• 10/2/2012 4:25:09 AM - 10/2/2012 1:09:10 PM Alerts: (8)
• Memory
• Memory Available MBytes (Alerts: 0)
• Memory Pages/sec (Alerts: 0)
• Network Interface
• Network Interface % Network Utilization (Alerts: 0)
• Network Interface Bytes Total/sec (Stats only)
• Network Interface Current Bandwidth (Stats only)
• PhysicalDisk
• PhysicalDisk Read Latency Analysis (Alerts: 60)
• PhysicalDisk Current Disk Queue Length (Alerts: 4)
• PhysicalDisk Avg. Disk Queue Length (Alerts: 30)
• Processor
• Processor % Processor Time (Alerts: 54)
• Processor % Privileged Time (Alerts: 9)
• Processor % Interrupt Time (Alerts: 0)
• Processor % DPC Time (Alerts: 0)
• SQLServer:Access Methods
• SQLServer:Access Methods Forwarded Records/sec (Alerts: 27)
• SQLServer:Access Methods Full Scans/sec (Stats only)
• SQLServer:Access Methods Page Splits/sec (Alerts: 29)
• SQLServer:Buffer Manager
• SQLServer:Buffer Manager Buffer cache hit ratio (Alerts: 1)
• SQLServer:Buffer Manager Lazy writes/sec (Alerts: 8)
• SQLServer:Buffer Manager Page life expectancy (Alerts: 6)
• SQLServer:General Statistics
• SQLServer:General Statistics User Connections (Alerts: 0)
• SQLServer:Locks
• SQLServer:Locks Lock Waits/sec (Stats only)
• SQLServer:Locks Lock Wait Time (ms) (Stats only)
• SQLServer:Memory Manager
• SQLServer:Memory Manager Memory Grants Pending (Alerts: 0)
• SQLServer:SQL Statistics
• SQLServer:SQL Statistics Batch Requests/sec (Alerts: 0)
• SQLServer:SQL Statistics SQL Compilations/sec (Alerts: 30)
• SQLServer:SQL Statistics SQL Re-Compilations/sec (Alerts: 1)
• System
• System Processor Queue Length (Alerts: 0)
• Disclaimer

Description: This section displays all of the alerts in chronological order.

Memory Available MBytes

Description: Available MBytes is the amount of physical RAM, in Megabytes, immediately available for allocation to a process or for system use. It is equal to the sum of memory assigned to the standby (cached), free and zero page lists. If this counter is low, then the computer is running low on physical RAM.
This analysis will alert a Warning if this counter's value is less than 10% of the RAM installed and will alert a critical if this counter's value is less than 100MB's. When Windows Server 2003 and newer has less then 100MBs of available RAM, it will go into a low memory condition and alert processes of this condition. If in a low memory condition the operating system will be more aggressive at working set trimming which pages out the physical RAM used by processes to the pagefile on the disk.

References:

• Measuring .NET Application Performance
• How To: Identify Functions causing a High User-mode CPU Bottleneck for Server Applications in a Production Environment

Memory Pages/sec

Description: This analysis checks to see if the Pages/sec is high while the system is in a low memory condition. If it is sustained at a high amount, then the system is might be running out of memory by trying to page the memory to the disk. Keep in mind that all hard page faults are counted in the pages/sec counter such as hard page faults caused by memory mapped files instead of page file hits. According to Wikipedia, memory-mapped files are a segment of virtual memory which has been assigned a direct byte-for-byte correlation with some portion of a file or file-like resource. This resource is typically a file that is physically present on-disk, but can also be a device, shared memory object, or other resource that the operating system can reference through a file descriptor. In other words, applications like Microsoft Word and Microsoft PowerPoint will not load entire documents into RAM. Instead, they memory map the file, so that when you navigate through the document, it loads portions of the document as needed. The act of loading portions of the document from disk to RAM as a memory mapped file causes a hard page fault which is counted in the pages/sec counter. See the article The Case of the Phantom Hard Page Faults. To determine if the hard page faults are actually hitting the page file, use Process Monitor with Advanced Ouput enabled to see how often the page file(s) are hit.
Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. It is the sum of Memory\Pages Input/sec and Memory\Pages Output/sec. It is counted in numbers of pages, so it can be compared to other counts of pages, such as Memory\Page Faults/sec, without conversion. It includes pages retrieved to satisfy faults in the file system cache (usually requested by applications) non-cached mapped memory files.

This counter should always be below 1000, therefore this analysis checks for values above 1000. Use this analysis in correlation with Available Memory Analysis and Memory Leak Analysis. If all are throwing alerts at the same time, then this may indicate the system is running out of memory and the suspected processes involved and follow analysis steps mentioned in the Memory Leak analysis.

Reference

• Ruling Out Memory-Bound Problems
  
• The Case of the Phantom Hard Page Faults
  

Network Interface % Network Utilization

Description: % Network Utilization doesn't exist as a normal performance counter, so this analysis uses multiplies \Network Interface(*)\Bytes Total/sec by 8 (to convert it to bits total/sec), divides it by \Network Interface(*)\Current Bandwidth, and multiplies the result by 100 to create a percentage. This analysis throws a warning alert when greater than 50 and throws a critical alert when greater than 80.

Network Interface Bytes Total/sec

Description: Bytes Total/sec is the rate at which bytes are sent and received over each network adapter, including framing characters. Network Interface\Bytes Total/sec is a sum of Network Interface\Bytes Received/sec and Network Interface\Bytes Sent/sec.

Network Interface Current Bandwidth

Description: Current Bandwidth is an estimate of the current bandwidth of the network interface in bits per second (BPS). For interfaces that do not vary in bandwidth or for those where no accurate estimation can be made, this value is the nominal bandwidth.

PhysicalDisk Read Latency Analysis

Description: Avg. Disk sec/Read is the average time, in seconds, of a read of data to the disk. This analysis determines if any of the physical disks are responding slowly.

If the response times are greater than .015 (15 milliseconds), then the disk subsystem is keeping up with demand, but does not have much overhead left.

If the response times are greater than .025 (25 milliseconds), then noticeable slow downs and performance issues affecting users may be occurring.

Reference:
Ruling Out Disk-Bound Problems
http://technet.microsoft.com/en-us/library/5bcdd349-dcc6-43eb-9dc3-54175f7061ad.aspx

PhysicalDisk Current Disk Queue Length

Description: Current Disk Queue Length is the number of requests outstanding on the disk at the time the performance data is collected. It also includes requests in service at the time of the collection. This is a instantaneous snapshot, not an average over the time interval. Multi-spindle disk devices can have multiple requests that are active at one time, but other concurrent requests are awaiting service. This counter might reflect a transitory high or low queue length, but if there is a sustained load on the disk drive, it is likely that this will be consistently high. Requests experience delays proportional to the length of this queue minus the number of spindles on the disks. For good performance, this difference should average less than two.

If the server is using an HBA (Host Bus Adapter: This is used to connect to a Storage Area Network SAN) and if the Current Disk Queue Length goes up to 32 frequently, then consider increasing the queue depth on the HBA to allow more concurrent I/O to the SAN. Please consult your SAN administrator before making any changes.

PhysicalDisk Avg. Disk Queue Length

Description: Avg. Disk Queue Length is the average number of both read and write requests that were queued for the selected disk during the sample interval.

Processor % Processor Time

Description: % Processor Time is the percentage of elapsed time that the processor spends to execute a non-Idle thread. It is calculated by measuring the duration of the idle thread is active in the sample interval, and subtracting that time from interval duration. This counter is the primary indicator of processor activity, and displays the average percentage of busy time observed during the sample interval. % Processor Time is the sum of % User Time and % Privileged Time unless there is hardware involvement in the form of interupts and/or DPCs.

This analysis creates a Warning alert for utilization greater than 50% on any processor and creates a critical alert for utilization greater than 80%.

If average processor utilization is high based on the thresholds witin this analysis, then check if it is high user mode CPU or high privileged mode. If high privileged mode CPU is suspected, then see the Privileged Mode CPU Analysis. If a user-mode processor bottleneck is suspected, then consider using a process profiler to analyze the functions causing the high CPU consumption. See How To: Identify Functions causing a High User-mode CPU Bottleneck for Server Applications in a Production Environment article in the references section for more information.
References:

• Measuring .NET Application Performance
• How To: Identify Functions causing a High User-mode CPU Bottleneck for Server Applications in a Production Environment

Processor % Privileged Time

Description: This counter indicates the percentage of time a thread runs in privileged mode also known as kernel mode. When your application calls operating system functions (for example to perform file or network I/O or to allocate memory), these operating system functions are executed in privileged mode.

High privileged mode CPU indicates that computer is spending too much time in system I/O versus real (user mode) work. % Privileged Time is the percentage of elapsed time that the process threads spent executing code in privileged mode. When a Windows system service in called, the service will often run in privileged mode to gain access to system-private data. Such data is protected from access by threads executing in user mode. Calls to the system can be explicit or implicit, such as page faults or interrupts. Unlike some early operating systems, Windows uses process boundaries for subsystem protection in addition to the traditional protection of user and privileged modes. Some work done by Windows on behalf of the application might appear in other subsystem processes in addition to the privileged time in the process.

This analysis throws a warning alert if privileged mode CPU is consuming more than 20% of total CPU and a critical alert if consuming More than 30% of total CPU.

Next steps
The CPU consumption might be caused by another busy resource such as network, memory, or disk I/O. High privileged mode CPU can also by caused by high amounts of Context Switches/second. See the High Context Switches/second analysis. The KernRate (KrView) tool can be used to profile the kernel to see what component is consuming the most kernel resources. To see more information about how KernRate can be used to analyze high priviledge mode CPU problems, see Mark Russinovich's blog entry in the references section below.

References:

• Measuring .NET Application Performance
• Mark's Blog : The Case of the System Process CPU Spikes

Processor % Interrupt Time

Description: % Interrupt Time is the time the processor spends receiving and servicing hardware interrupts during sample intervals. This value is an indirect indicator of the activity of devices that generate interrupts, such as the system clock, the mouse, disk drivers, data communication lines, network interface cards and other peripheral devices. These devices normally interrupt the processor when they have completed a task or require attention. Normal thread execution is suspended during interrupts. Most system clocks interrupt the processor every 10 milliseconds, creating a background of interrupt activity. A dramatic increase in this counter indicates potential hardware problems.

This analysis checks for % Interrupt Time greater than 30%. If this occurs, then consider updating devices drivers for hardware that correlates to this alert.

References:
Measuring .NET Application Performance
http://msdn2.microsoft.com/en-us/library/ms998579.aspx

Processor % DPC Time

Description: % DPC Time is the percentage of time that the processor spent receiving and servicing deferred procedure calls (DPCs) during the sample interval. DPCs are interrupts that run at a lower priority than standard interrupts. % DPC Time is a component of % Privileged Time because DPCs are executed in privileged mode. They are counted separately and are not a component of the interrupt counters. This counter displays the average busy time as a percentage of the sample time.

SQLServer:Access Methods Forwarded Records/sec

Description: SQLServer:Access Methods Forwarded Records/sec

Description: Rows with varchar columns on tables without a clustered index can experience expansion when varchar values are updated with a longer string. In the case where the row cannot fit in the existing page, the row migrates and access to the row will traverse a pointer. Forwarded records occur when a data record in a heap increases in size and the records current page does not have the space to store the size increase. The record is moved to a new location, becoming a forwarded record, and the forwarding record is left in the original location to point to the real location of the record. The forwarded record points back to the forwarding record in case its location ever needs to change again. Access Methods Forwarded Records/sec measures the number of records fetched through forwarded record pointers which are due to tables without a clustered index. A forwarded record is basically a pointer. For instance, if you start with a short row, and update the row creating a wider row, the row might not fit on the data page. A pointer is put in its location and the row is forwarded to another page. This is done as a performance optimization so that all the non-clustered indexes on the heap do not have to be altered with the new location of the heap record. If a table has lots of forwarded records, scanning the table can be very inefficient. Also, rows with varchar columns can experience expansion when varchar values are updated with a longer string. In the case where the row cannot fit in the existing page, the row migrates and access to the row will traverse a pointer. Forwarded Records only occurs on heaps which are tables without clustered indexes. Threshold: (Yellow) - This value should not be greater than 10% of the number of Batch Requests/Sec

Next Steps:
Look at code to determine where the short row is inserted followed by an update. Forwarded records can be avoided by:

Using default values so that an update does not result in a longer row that is the root cause of forwarded records.

Using Char instead of Varchar. Using Char creates a fixed length so that an update does not result in a longer row.

Evaluate clustered indexes for heap tables.

In cases where clustered indexes cannot be used, drop non-clustered indexes, build a clustered index to reorganize pages and rows, drop the clustered index, and then recreate non-clustered indexes.

Learn to use the sys.dm_db_index_physical_stats dynamic management view (DMV) to find forwarded records. In the sys.dm_db_index_physical_stats DMV there is a column used called the forwarded_record_count which counts the number of records in a heap that have forward pointers to another data location. (This state occurs during an update, when there is not enough room to store the new row in the original location.)
Reference:
SQL Server Storage Engine http://blogs.msdn.com/sqlserverstorageengine/archive/2006/09/19/761437.aspx
Forwarding and forwarded records, and the back-pointer size http://www.sqlskills.com/BLOGS/PAUL/post/Forwarding-and-forwarded-records-and-the-back-pointer-size.aspx
sys.dm_db_index_physical_stats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188917.aspx
SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426.aspx

SQLServer:Access Methods Full Scans/sec

Description: SQLServer:Access Methods Full Scans/sec
Description: This counter monitors the number of full scans on base tables or indexes. High values indicate that we may be having performance issues due to table / index page scans. If we see high CPU and / or low Page Life Expectancy (PLE) then we need to investigate this counter; however, if full scans are on small tables we can ignore this counter. A few of the main causes of high Full Scans/sec are missing indexes, too many rows requested, queries with missing indexes, or too many rows requested will have a large number of logical reads and an increased CPU time. This analysis throws a Warning alert if the ratio of Index Searches/sec to Full Scans/sec is less than 1000 to 1 and if there are more than 1000 Index Searches/sec.

Threshold:
Yellow: A ratio of more than 1 full scan for every 1000 index searches. The value of Index Searches/sec and Full Scans/sec should be greater than 1000.

Formula: (AvgSQLServerAccessMethodsIndexSearchessecAll / AvgSQLServerAccessMethods_FullScanssec) < 1000

Next Steps: The main causes of high Full Scans/sec are:

Missing indexes

Too many rows requested Queries with missing indexes or too many rows requested will have a large number of logical reads and an increased CPU time. As mentioned, if there is a high level of Full Scans to Index Searches, then it is important to also check the following: See PERFMON SQLServer:Buffer Manager performance counters for memory pressure:

Page Life Expectancy

Checkpoint pages/sec

Lazy writes/sec A high number of scans can cause buffer pool pressure (as indicated with low PLE and a higher Lazy Writes / sec count). Memory pressure will quickly manifest into disk pressure, so also check: See PERFMON Physical Disk performance counters:

Disk sec/read

Disk sec/write Note: Identify disk bottlenecks by using Performance Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN output. Also refer to the sys.dm_io_virtual_file_stats dynamic management view (DMV) to track io_stalls to help identify IO bottlenecks. To back up and support this information, compare the counters to sys.dm_os_wait_stats output. If you see high values in perfmon, you may also see high waits for the following:

ASYNC_IO_COMPLETION

IO_COMPLETION

PAGEIOLATCH_* (Data page I/O completion waits appear as PAGEIOLATCH_* waits) Reactively, SQL Profiler can be used to identify which SQL statements are causing scans. Use the scans event class and events scan:started and scan:completed. Include the object ID data column. Save the profiler trace to a file and then convert it to trace table. You can then search for the scans event. The scan:completed event provides the associated IO so that you can also search for high reads, writes, and duration.
Reference:
SQL Server, Access Methods Object http://msdn.microsoft.com/en-us/library/ms177426.aspx
SQL Server 2005 Waits and Queues http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
Wait Types and Correlation to Other Performance Info http://www.sqlmag.com/Files/09/40925/Webtable_01.doc

SQLServer:Access Methods Page Splits/sec

Description: SQLServer:Access Methods Page Splits/sec
Description: The number of page splits per second that occurs as the result of overflowing index pages. When a record is inserted into an index, it must be inserted in order. If the data page is full, the page splits in order to maintain the appropriate order. A high value for this counter may warrant the consideration of a lower fill factor. This value should be as low as possible. Heavily fragmented indexes may be the result of high page splits/sec.
Threshold:
Yellow: A ratio of more than 1 page split for every 20 batch requests Next Steps: If the number of page splits is high, consider increasing the fillfactor of your indexes. An increased fillfactor helps to reduce page splits by increasing the amount of free space on each page. Note: This counter also includes new page allocations, and does not mean there is an issue.
Reference:
SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426(v=SQL.105).aspx

SQLServer:Buffer Manager Buffer cache hit ratio

Description: \SQLServer:Buffer Manager\Buffer cache hit ratio

Description: The Buffer Cache Hit Ratio measures the percentage of pages that were found in the buffer pool without having to incur a read from disk. This counter indicates how often SQL Server goes to the buffer, not the hard disk, to get data. The higher this ratio, the better. A high ratio, close to 100% indicates that SQL Server did not have to go to the hard disk often to fetch data, and performance overall is boosted. If the Buffer Cache Hit Ratio was 100% that would suggest that all of the pages are being accessed from cache and does not require trips to disk, because of the optimistic read ahead mechanism, this is not exactly the case. When a user session wants to read data from the database, it will read directly from the SQL Server buffer cache (a logical read), or, if the buffer cache does not have the data that is requested, the data will be read into the buffer cache from disk (a physical read) and then from the buffer cache. If the requested data is in the buffer cache, then it is called a 'buffer hit'. If the data is not in the buffer cache it is called a 'buffer miss'. The ratio of buffer hits to total buffer requests is called the buffer cache hit ratio as can be seen from the following:

Cache Hit Ratio = (Logical Reads - Physical Reads)/Logical Reads

A read from memory takes approximately 100 nanoseconds, while a read from disk takes about 8 milliseconds or more. 1 millisecond = 1,000,000 nanoseconds The important point about SQL Server read operations is that when selecting data from the database, the user will wait on the complete read operation including all of the physical reads. The time is takes to select from the database depends on how much data will be read and how long it takes for those reads to occur. Even with cache reads, the time it takes to read a large amount of data can be significant. With physical reads, the time will be even longer. There are a few considerations to be aware of regarding the Buffer Cache Hit Ratio counter. First, unlike many of the other counters available for monitoring SQL Server, this counter averages the Buffer Cache Hit Ratio from the time the instance of SQL Server was started. In other words, this counter is not a real-time measurement, but an average. Secondly, the buffer cache hit ratio may be skewed by the read ahead mechanism. Read Ahead Reads are pages that were read into cache while the query was processed. Because of the read ahead mechanism, you should not infer from a high buffer cache hit ratio that SQL Server is not suffering from memory pressure or at least could not benefit from additional memory.
Threshold:
Yellow: Less than 97 percent buffer cache hit ratio
Next Steps:
Run expensive queries through the Database Tuning Advisor (DTA), add additional memory, and look for queries with a high number of logical reads and consider tuning and potentially rewriting them.
Reference:
SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426.aspx